﻿
create table Users(
	UserID int IDENTITY(1,1) not null ,
	Username nvarchar(50) not null,
	Password nvarchar(50) not null,
	Primary Key (UserID)	
);

create table Roles(
	 RoleID int identity(1,1) not null,
	 RoleName nvarchar(50) not null,
	 Primary key(RoleID)	
);

create table UsersInRoles(
	 UserRoleID int identity(1,1) not null,
	 RoleRef int not null,
	 UserRef int not null,
	 Primary key(UserRoleID)	
);


if not exists(select 1 from sys.objects where name='UK_User_Username')
	alter table Users
	Add CONSTRAINT UK_User_Username unique(Username)
GO

--if not exists(select 1 from sys.objects where name='UK_Role_RoleName')
	alter table Roles
	Add CONSTRAINT UK_Role_RoleName unique(Rolename)
GO

--if not exists(select 1 from sys.objects where name='FK_UsersInRoles_UserRef')
	alter table UsersInRoles
	add constraint FK_UsersInRoles_UserRef foreign key(UserRef)
	references Users (UserID)
GO

--if not exists(select 1 from sys.objects where name='FK_UsersInRoles_RoleRef')
	alter table UsersInRoles
	add constraint FK_UsersInRoles_RoleRef foreign key(RoleRef)
	references Roles (RoleID)
GO